DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS surveys;
DROP TABLE IF EXISTS questions;
DROP TABLE IF EXISTS answers;
DROP TABLE IF EXISTS users_surveys;

CREATE TABLE users(userNick VARCHAR(30) NOT NULL PRIMARY KEY, 
	userPass VARCHAR(30), 
	CONSTRAINT ctUserUnique UNIQUE(userNick)
);

CREATE TABLE surveys(survId BIGINT NOT NULL PRIMARY KEY,
	survName VARCHAR(50),
	survActive BOOLEAN,
	survUserNick VARCHAR(30) NOT NULL,
	FOREIGN KEY(survUserNick) REFERENCES users(userNick)
);

CREATE TABLE questions(questId BIGINT NOT NULL PRIMARY KEY,
	questText VARCHAR(60) NOT NULL,
	questSurvId BIGINT NOT NULL,
	FOREIGN KEY (questSurvId) REFERENCES surveys(survId)
);

CREATE TABLE answers(answId BIGINT NOT NULL PRIMARY KEY,
	answText VARCHAR(60) NOT NULL,
	answQuestId BIGINT NOT NULL,
	FOREIGN KEY (answQuestId) REFERENCES questions(questId)
);

CREATE TABLE users_surveys(us_svUserNick VARCHAR(30) NOT NULL,
	us_svSurvId BIGINT NOT NULL,
	us_svAnswId BIGINT NOT NULL,
	PRIMARY KEY(us_svUserNick, us_svSurvId, us_svAnswId),
	FOREIGN KEY (us_svUserNick)REFERENCES users(userNick),
	FOREIGN KEY (us_svSurvId) REFERENCES surveys(survId),
	FOREIGN KEY (us_svAnswId) REFERENCES answers(answId)
);
	